package com.kxs.stu_manager.model;

import com.kxs.stu_manager.global.JdbcUtils;
import com.kxs.stu_manager.global.Student;
import com.kxs.stu_manager.views.StuPage;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 * @ClassName StuModel
 * @Description: TODO 学生的模型层
 * @Author: 2119475835@qq.com
 * @Date: 2023/1/9
 */
public class StuModel {
    private Connection conn = null;
    private PreparedStatement pst = null;
    private ResultSet rs = null;

    /**
     * 获取全部学生信息
     * @return 学生集合
     * @throws SQLException
     */
    public ArrayList<Student> selAllStu() throws SQLException {
        ArrayList<Student> stuList = new ArrayList<>();

        // 建立连接
        conn = JdbcUtils.getConnection();
        // 使用定义好的 sql 语句来实例化 pst 对象，对 sql 做一个预处理（字符串化）
        String sql = "select `id`, `name`, `sex`, `age`, `score`, `tel`, `classid` from `stu`";
        pst = conn.prepareStatement(sql);
        // 执行sql语句
        rs = pst.executeQuery();
        // 处理数据
        while (rs.next()){
            stuList.add(
                    new Student(
                            rs.getInt("id"),
                            rs.getString("name"),
                            rs.getString("sex"),
                            rs.getInt("age"),
                            rs.getFloat("score"),
                            rs.getString("tel"),
                            rs.getString("classid")
                    )
            );
        }
        // 释放资源
        JdbcUtils.free(rs, pst, conn);
        return stuList;
    }

    /**
     * 根据ID获取学生信息
     * @param id 要查询的学生ID
     * @return 学生信息集合
     */
    public ArrayList<Student> selStuByID(int id) throws SQLException {
        ArrayList<Student> stuList = new ArrayList<>();

        conn = JdbcUtils.getConnection();

        String sql = "select `id`, `name`, `sex`, `age`, `score`, `tel`, `classid` from `stu` where `id` = ?";
        pst = conn.prepareStatement(sql);
        // 占位符绑定
        pst.setInt(1, id);

        rs = pst.executeQuery();

        // 处理结果集
        if(rs.next()){
            stuList.add(
                    new Student(
                            rs.getInt("id"),
                            rs.getString("name"),
                            rs.getString("sex"),
                            rs.getInt("age"),
                            rs.getFloat("score"),
                            rs.getString("tel"),
                            rs.getString("classid")
                    )
            );
        }
        return stuList;
    }

    /**
     * 根据姓名获取学生信息
     * @param name 要查询的学生姓名
     * @return 查询到的学生信息集合
     * @throws SQLException
     */
    public ArrayList<Student> selStuByName(String name) throws SQLException {
        ArrayList<Student> stuList = new ArrayList<>();

        conn = JdbcUtils.getConnection();

        String sql = "select `id`, `name`, `sex`, `age`, `score`, `tel`, `classid` from `stu` where `name` = ?";
        pst = conn.prepareStatement(sql);
        // 占位符绑定
        pst.setString(1, name);

        rs = pst.executeQuery();

        // 处理结果集
        while (rs.next()){
            stuList.add(
                    new Student(
                            rs.getInt("id"),
                            rs.getString("name"),
                            rs.getString("sex"),
                            rs.getInt("age"),
                            rs.getFloat("score"),
                            rs.getString("tel"),
                            rs.getString("classid")
                    )
            );
        }
        return stuList;
    }

    /**
     * 根据性别获取学生信息
     * @param sex 要查询的性别
     * @return 查询到的学生信息集合
     * @throws SQLException
     */
    public ArrayList<Student> selStuBySex(String sex) throws SQLException {
        ArrayList<Student> stuList = new ArrayList<>();

        conn = JdbcUtils.getConnection();

        String sql = "select `id`, `name`, `sex`, `age`, `score`, `tel`, `classid` from `stu` where `sex` = ?";
        pst = conn.prepareStatement(sql);
        // 占位符绑定
        pst.setString(1, sex);

        rs = pst.executeQuery();

        // 处理结果集
        while (rs.next()){
            stuList.add(
                    new Student(
                            rs.getInt("id"),
                            rs.getString("name"),
                            rs.getString("sex"),
                            rs.getInt("age"),
                            rs.getFloat("score"),
                            rs.getString("tel"),
                            rs.getString("classid")
                    )
            );
        }
        return stuList;
    }

    /**
     * 根据电话获取学生信息
     * @param tel 要查询的电话
     * @return 查询到的学生信息集合
     * @throws SQLException
     */
    public ArrayList<Student> selStuByTel(String tel) throws SQLException {
        ArrayList<Student> stuList = new ArrayList<>();

        conn = JdbcUtils.getConnection();

        String sql = "select `id`, `name`, `sex`, `age`, `score`, `tel`, `classid` from `stu` where `tel` = ?";
        pst = conn.prepareStatement(sql);
        // 占位符绑定
        pst.setString(1, tel);

        rs = pst.executeQuery();

        // 处理结果集
        if(rs.next()){ // tel字段有唯一性索引，这里用if即可
            stuList.add(
                    new Student(
                            rs.getInt("id"),
                            rs.getString("name"),
                            rs.getString("sex"),
                            rs.getInt("age"),
                            rs.getFloat("score"),
                            rs.getString("tel"),
                            rs.getString("classid")
                    )
            );
        }
        return stuList;
    }

    /**
     * 根据班级进行查询
     * @param classid 用户输入班级
     * @return 查询到的学生信息
     * @throws SQLException
     */
    public ArrayList<Student> selStuByClass(String classid) throws SQLException {
        ArrayList<Student> stuList = new ArrayList<>();

        conn = JdbcUtils.getConnection();

        String sql = "select `id`, `name`, `sex`, `age`, `score`, `tel`, `classid` from `stu` where `classid` like \"%\"?\"%\"";
        pst = conn.prepareStatement(sql);
        // 占位符绑定
        pst.setString(1, classid);

        rs = pst.executeQuery();

        // 处理结果集
        while (rs.next()){
            stuList.add(
                    new Student(
                            rs.getInt("id"),
                            rs.getString("name"),
                            rs.getString("sex"),
                            rs.getInt("age"),
                            rs.getFloat("score"),
                            rs.getString("tel"),
                            rs.getString("classid")
                    )
            );
        }
        return stuList;
    }

    /**
     * 通过年龄获取学生信息
     * @param age 要查询的年龄
     * @return 查询到的学生信息集合
     * @throws SQLException
     */
    public ArrayList<Student> selStuByAge(int age) throws SQLException {
        ArrayList<Student> stuList = new ArrayList<>();

        conn = JdbcUtils.getConnection();

        String sql = "select `id`, `name`, `sex`, `age`, `score`, `tel`, `classid` from `stu` where `age` = ?";
        pst = conn.prepareStatement(sql);
        // 占位符绑定
        pst.setInt(1, age);

        rs = pst.executeQuery();

        // 处理结果集
        while (rs.next()){
            stuList.add(
                    new Student(
                            rs.getInt("id"),
                            rs.getString("name"),
                            rs.getString("sex"),
                            rs.getInt("age"),
                            rs.getFloat("score"),
                            rs.getString("tel"),
                            rs.getString("classid")
                    )
            );
        }
        return stuList;
    }

    /**
     * 根据区间来获取学生数据 age[0] 最小值， age[1] 最大值
     * @param age 年龄区间
     * @return 学生信息集合
     * @throws SQLException
     */
    public ArrayList<Student> selStuByAge(int[] age) throws SQLException {
        ArrayList<Student> stuList = new ArrayList<>();

        conn = JdbcUtils.getConnection();

        String sql = "select `id`, `name`, `sex`, `age`, `score`, `tel`, `classid` from `stu` where `age` between ? and ?";
        pst = conn.prepareStatement(sql);
        // 占位符绑定
        pst.setInt(1, age[0]);
        pst.setInt(2, age[1]);

        rs = pst.executeQuery();

        // 处理结果集
        while (rs.next()){
            stuList.add(
                    new Student(
                            rs.getInt("id"),
                            rs.getString("name"),
                            rs.getString("sex"),
                            rs.getInt("age"),
                            rs.getFloat("score"),
                            rs.getString("tel"),
                            rs.getString("classid")
                    )
            );
        }
        return stuList;
    }

    /**
     * 通过成绩获取学生信息
     * @param score 要查询的成绩
     * @return 学生信息集合
     */
    public ArrayList<Student> selStuByScore(float score) throws SQLException {
        ArrayList<Student> stuList = new ArrayList<>();

        conn = JdbcUtils.getConnection();

        String sql = "select `id`, `name`, `sex`, `age`, `score`, `tel`, `classid` from `stu` where `score` = ?";
        pst = conn.prepareStatement(sql);
        // 占位符绑定
        pst.setFloat(1, score);

        rs = pst.executeQuery();

        // 处理结果集
        while (rs.next()){
            stuList.add(
                    new Student(
                            rs.getInt("id"),
                            rs.getString("name"),
                            rs.getString("sex"),
                            rs.getInt("age"),
                            rs.getFloat("score"),
                            rs.getString("tel"),
                            rs.getString("classid")
                    )
            );
        }
        return stuList;
    }

    /**
     * 根据成绩范围进行查询
     * @param score 成绩范围
     * @return 学生信息集合
     */
    public ArrayList<Student> selStuByScore(int[] score) throws SQLException {
        ArrayList<Student> stuList = new ArrayList<>();

        conn = JdbcUtils.getConnection();

        String sql = "select `id`, `name`, `sex`, `age`, `score`, `tel`, `classid` from `stu` where `score` between ? and ?";
        pst = conn.prepareStatement(sql);
        // 占位符绑定
        pst.setInt(1, score[0]);
        pst.setInt(2, score[1]);

        rs = pst.executeQuery();

        // 处理结果集
        while (rs.next()){
            stuList.add(
                    new Student(
                            rs.getInt("id"),
                            rs.getString("name"),
                            rs.getString("sex"),
                            rs.getInt("age"),
                            rs.getFloat("score"),
                            rs.getString("tel"),
                            rs.getString("classid")
                    )
            );
        }
        return stuList;
    }

    /**
     * 将学生对象添加到数据库中
     * @param stu 要添加的学生对象
     * @return 是否继续添加 true 继续 false 退出
     */
    public boolean addStu(Student stu) throws SQLException {
        if(null == stu){
            return false;
        }

        conn = JdbcUtils.getConnection();

        String sql = "insert into `stu`(`name`, `sex`, `age`, `score`, `tel`, `classid`) value (?, ?, ?, ?, ?, ?)";
        pst = conn.prepareStatement(sql);
        // 占位符绑定
        pst.setString(1, stu.getName());
        pst.setString(2, stu.getSex());
        pst.setInt(3, stu.getAge());
        pst.setFloat(4, stu.getScore());
        pst.setString(5, stu.getTel());
        pst.setString(6, stu.getClassid());

        try{
            int rows = pst.executeUpdate();

            if(rows > 0){
                StuPage.success("添加信息成功！~");
            }else {
                StuPage.failed("添加信息失败！~");
            }
        }catch (SQLException e){
            StuPage.failed("电话号码已存在");
        }finally {
            JdbcUtils.free(rs, pst, conn);
        }
        return true;
    }

    /**
     * 将该学号的学生删除
     * @param delStuID 要删除的学生ID
     * @throws SQLException
     */
    public void doDelStu(int delStuID) throws SQLException {
        conn = JdbcUtils.getConnection();

        String sql = "delete from `stu` where `id` = ?";
        pst = conn.prepareStatement(sql);
        // 占位符绑定
        pst.setFloat(1, delStuID);

        int rows = pst.executeUpdate();
        if(rows > 0){
            StuPage.success("删除成功！~");
        }else{
            StuPage.failed("删除失败！~");
        }

        JdbcUtils.free(rs, pst, conn);

    }

    /**
     * 根据新的学生信息对象进行修改
     * @param newStu 新的学生信息对象
     * @throws SQLException
     */
    public void updateStu(Student newStu) throws SQLException {
        conn = JdbcUtils.getConnection();

        String sql = "update `stu` set `name` = ?, `sex` = ?, `age` = ?, `score` = ?, `tel` = ?, `classid` = ? where `id` = ?";
        pst = conn.prepareStatement(sql);
        // 占位符绑定
        pst.setString(1, newStu.getName());
        pst.setString(2, newStu.getSex());
        pst.setInt(3, newStu.getAge());
        pst.setFloat(4, newStu.getScore());
        pst.setString(5, newStu.getTel());
        pst.setString(6, newStu.getClassid());
        pst.setInt(7, newStu.getId());

        try{
            int rows = pst.executeUpdate();
            if(rows > 0){
                StuPage.success("修改成功！~");
            }else{
                StuPage.failed("修改失败！~");
            }
        }catch (SQLException e){
            StuPage.failed("修改的学号已存在！~");
        }finally {
            JdbcUtils.free(rs, pst, conn);
        }
    }
}
